--USE scm_main;
-- 需要先创建拆分字符串存储过程（split_str）
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_ot_detail_del')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_ot_detail_del
GO
CREATE PROCEDURE bd_proc_ot_detail_del
	@order_id varchar(200),
	@user_serial_str varchar(max),
	@ip varchar(200),
	@operator varchar(200),
	@reason varchar(max)
as
BEGIN
	--说明：加班取消分两种：整单取消、明细取消；(区分：以@user_serial_str是否为null为标准，为null则整单取消，反之明细取消)
				--整单取消：整理需要取消的明细信息时，需要过滤之前明细取消过的记录；，修改主单标识，并保存取消信息
				--明细取消：只整理该单的这些人的明细信息。修改明细标识，并保存取消信息

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY

			CREATE TABLE #order_detail_info(
				id INT IDENTITY,
				order_id int,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				date date
			)

      CREATE TABLE #rest_info(
        id int identity,
        user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				ot_minute int,--申请的时间
        duration int,--转调休时间
        date date,
				if_operation int
      )

		-- 开启事务
			BEGIN TRAN

        -- 如果用户编号不为空 封装处理数据(说明是明细取消)
				if(isnull(@user_serial_str,'') <> '')
				BEGIN
					insert into #order_detail_info(order_id,user_serial)
					select @order_id,a.value
					from string_split(@user_serial_str,',') a
				END
        else BEGIN
				  -- 如果用户编号为空 封装处理数据
					insert into #order_detail_info(order_id,user_serial)
					select @order_id,a.user_serial
					from lr_jiaba a
					where a.order_id = @order_id and a.is_delete !=1
				END

        -- 更新加班详情单参数
        update a set a.date = convert(varchar(10),b.jbrq,120)
        from #order_detail_info a
        inner join lr_jiaba b on a.order_id = b.order_id and a.user_serial = b.user_serial

				-- 考勤日志
				insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj)
				select b.user_serial,convert(varchar(10),b.jbrq,120),1,3,2,0,0,b.bh,b.kssj,b.jssj,b.jsjg,@operator,getdate()
				from #order_detail_info a
				inner join lr_jiaba b on a.order_id = b.order_id and a.user_serial = b.user_serial


				-- 记录日志
				insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select 10,2,CONCAT(@operator,'取消',c.user_lname,b.kssj,'---',b.jssj,'加班记录',',单ID:',@order_id),@operator,GETDATE(),'',@ip,''
				from #order_detail_info a
				inner join lr_jiaba b on a.order_id = b.order_id and a.user_serial = b.user_serial
				left join dt_user c on b.user_serial = c.user_serial


        -- 查询需要修改的记录(有调休的数据)
				--一、取消节点按结果分为两类：1确认加班工时前（申请后、审批后）、2确认加班工时之后；
				--二、处理方式（前提：每人每天只能申请一次加班）：
				-- 确认加班工时前取消：因未实际入账，只需将bd_nianjia(假期额度表)中的real_txts字段减去对应的加班申请值，另将该条明细标记为已取消（若是整单取消，则加班单也要标记已取消），并记录取消人、取消时间、取消原因。最后重新计算考勤。
				-- 	确认加班工时之后：因已经实际入账，则需要关联bd_kq_timecard(考勤表)获取实际入账值（ot_to_rest_hours）,则应将bd_nianjia(假期额度表)中的txts、real_txts、tx_count同时减去实际入账值，并记录在bd_kq_holidays(假期明细表)中。同时将该条明细标记为已取消（若是整单取消，则加班单也要标记已取消），并记录取消人、取消时间、取消原因。。最后重新计算考勤。


        insert into #rest_info(user_serial,ot_minute,duration,date,if_operation)
        select a.user_serial,b.ot_hours,b.ot_to_rest_hours,a.date,b.if_operation
        from #order_detail_info a
        inner join bd_kq_timecard b on a.user_serial = b.user_serial and a.date = b.card_date
        -- where isnull(b.ot_to_rest_hours,0) > 0

        -- 恢复无加班数据
        update a set a.ot_bill_id=0,a.ot_bill_status=0,a.ot_type_id=0,a.ot_type_name=0,a.ot_begin=0,a.ot_end=0,a.ot_hours=0,a.ot_is_eating=0,a.ot_pending_hours=0,ot_normal_hours=0,a.ot_weekend_hours=0,ot_holiday_hours=0,ot_to_rest_hours=0
        from bd_kq_timecard a
        inner join #order_detail_info b on a.user_serial = b.user_serial and a.card_date = b.date


        -- 更新员工假期表(已确认加班) 减去实际加班转调休时间
        update a set a.txcount = (a.txcount - b.duration), a.txts = (a.txts - b.duration),a.real_txts = (a.real_txts-b.duration)
        from bd_nianjia a
        inner join #rest_info b on a.user_serial = b.user_serial
				where b.if_operation = 1


        -- 插入假期明细表数据(已确认加班)
				--获取单子审批状态
				DECLARE @orderStatus int =0;
				select @orderStatus = status from bd_over_time_order where order_id = @order_id;

        insert into bd_kq_holidays (user_serial,dept_serial,dept_name,ho_year,ho_month,ho_date,ho_type,use_time,remain_time,vacation_total,create_by,related_id,related_result)
        select a.user_serial, b.user_dep,user_depname,year(c.date),month(c.date),c.date,2,-duration,a.txts,a.txcount,@operator,@order_id,@orderStatus
        from bd_nianjia a
        inner join dt_user b on a.user_serial= b.user_serial
        inner join #rest_info c on b.user_serial = c.user_serial
				where c.if_operation = 1

				-- 更新员工假期表(未确认加班),仅仅减去real_txts字段 申请加班时间

				update a set a.real_txts = (a.real_txts-b.ot_minute)
        from bd_nianjia a
        inner join #rest_info b on a.user_serial = b.user_serial
				where isnull(b.if_operation,0) != 1


				-- 如果用户编号不为空 封装处理数据(说明是明细取消)
				if(isnull(@user_serial_str,'') <> '')
				BEGIN
					-- 逻辑删除加班详情单
					update a set a.is_delete = 1,
										a.remove_user = @operator,
										a.remove_time = getDate(),
										a.return_remark = @reason
					from lr_jiaba a
					inner join #order_detail_info b on a.order_id = b.order_id and a.user_serial = b.user_serial

					set @result = @@ROWCOUNT;

					 -- 更新加班单 总人数和总时长
					update a set a.sum_user = (a.sum_user - b.sum_user), a.sum_minute = (a.sum_minute - b.sum_minute)
					from bd_over_time_order a
					inner join (
						select a.order_id,count(0) as sum_user,sum(jsjg) as sum_minute
						from lr_jiaba a
						inner join (select order_id,user_serial from #order_detail_info) b on a.order_id = b.order_id and a.user_serial = b.user_serial
						group by a.order_id
					) b on a.order_id = b.order_id
				END
				--整单删除
				ELSE BEGIN
					-- 逻辑删除加班详情单
					update a set a.is_delete = 1
					from lr_jiaba a
					inner join #order_detail_info b on a.order_id = b.order_id and a.user_serial = b.user_serial;

					--逻辑删除加班单
					update a set a.is_delete = 1,
										a.remove_user = @operator,
										a.remove_time = getDate(),
										a.return_remark = @reason
					from bd_over_time_order a
					where a.order_id = @order_id;

					set @result = @@ROWCOUNT;
				END

				-- 准备调用考勤计算

			  declare @us_str_tc varchar(max) = '';
			  declare @date_tc date = null;

        select @us_str_tc += iif(isnull(user_serial,'') = '','',concat(',',user_serial)) from #order_detail_info

        -- 去除最前面的逗号
        set @us_str_tc = iif(left(@us_str_tc,1) = ',',right(@us_str_tc,len(@us_str_tc)-1),@us_str_tc)

        -- 一个加班单的日期都是相同的
        select top 1 @date_tc = date from #order_detail_info

        --计算考勤(3代表加班调用)
        EXEC bd_proc_timecard @date_tc, @us_str_tc, 3


		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
		END CATCH


--  清除临时表
	drop table #order_detail_info;
	drop table #rest_info;

	select @result as result

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;